<?php
class Electoral_roll_model extends CI_Model
{

	public function __construct()
	{
		$this->load->database();
	}

/* No longer makes sense for polling district rolls
	public function get($id)
	{
		$sql = "select w.display_name, er.compile_date " .
			   "from electoral_roll er, ward w " .
			   "where er.id = ? " . 
			   "and er.ward = w.id " .
			   "order by w.display_name, er.compile_date" ;

		$query = $this->db->query($sql, array($id));

		return $query->row();
	}
*/

/* Not sure this was ever useful
	public function get_all_rolls()
	{
		$sql = "select er.id, w.display_name, er.compile_date " .
			   "from electoral_roll er, ward w " .
			   "where er.ward = w.id " .
			   "order by w.display_name, er.compile_date" ;

		$query = $this->db->query($sql);

		return $query->result();
	}
*/

    public function get_zone($id)
    {
        $sql = "select display_name " .
               "from electoral_zone er " .
               "where id = ? "; 

        $query = $this->db->query($sql, array($id));

        return $query->row();
    }
    
    public function get_electoral_zones ($type)
    {
        $sql = "select id, display_name " .
               "from electoral_zone " .
               "where type = ? " .
               "order by display_name asc";
               
        $query = $this->db->query($sql, array($type));
               
        return $query->result();
    }
/*    
	public function get_latest_rolls()
	{
		$sql = "select er.id, w.display_name, er.compile_date " .
			   "from electoral_roll er, ward w " .
			   "where er.ward = w.id " .
			   "and er.compile_date = (select max(compile_date) " .
			   "from  electoral_roll " .
			   "where ward = er.ward) " .
			   "order by w.display_name";

		$query = $this->db->query($sql);

		return $query->result();
	}
*/
	public function get_entries($zone,$num,$offset)
	{
		$sql = $this->entries_sql() .
		       "       limit ?, ?";

		$query = $this->db->query($sql, array($zone, $offset, $num));

		return $query->result();
	}

	public function count_entries($zone)
	{
		$sql = $this->entries_sql() ;

		$query = $this->db->query($sql, array($zone));

		return $query->num_rows();
	}

    protected function entries_sql()
    {
        $sql = "select ere.polling_district, ere.voter_number, ere.voter_number_suffix, ere.person, p.surname, p.first_names, " .
               "       a.address1, a.address2, a.address3, a.address4, a.address5, a.postcode, ere.vote_type " .
               "       from electoral_roll_entry ere, zone_district zd, polling_district pd, person p, address a " . 
               "       where zd.zone = ? " .
               "       and zd.polling_district = pd.id " .
               "       and pd.current_roll = ere.electoral_roll " .
               "       and ere.person = p.id " .
               "       and ere.address = a.id " .
               "       order by ere.electoral_roll, ere.id ";
               
        return $sql;
    }
/*
    protected function entries_sql()
    {
        $sql = "select ere.polling_district, ere.voter_number, ere.voter_number_suffix, ere.person, p.surname, p.first_names, " .
               "       a.address1, a.address2, a.address3, a.address4, a.address5, a.postcode, ere.vote_type" .
               "       from electoral_roll_entry ere, person p, address a " .
               "       where ere.electoral_roll = ?" .
               "       and ere.person = p.id " .
               "       and ere.address = a.id " .
               "       order by ere.id " ;

        return $sql;
    }
*/
	public function get_quick_search($roll, $name, $address, $num, $offset)
	{
		$sql = $this->quick_search_sql($roll, $name, $address) .
		       "       limit ?, ?";

		$query = $this->db->query($sql, array($offset, $num));

		return $query->result();
	}

	public function count_quick_search($roll, $name, $address)
	{
		$sql = $this->quick_search_sql($roll, $name, $address) ;

//		echo "sql: " . $sql . "<br/>";

		$query = $this->db->query($sql);

		return $query->num_rows();
	}

	protected function quick_search_sql ($zone, $name, $address)
	{
        $sql = "select ere.polling_district, ere.voter_number, ere.voter_number_suffix, ere.person, p.surname, p.first_names, " .
               "       a.address1, a.address2, a.address3, a.address4, a.address5, a.postcode, ere.vote_type " .
               "       from electoral_roll_entry ere, zone_district zd, polling_district pd, person p, address a " . 
               "       where zd.zone = $zone " .
               "       and zd.polling_district = pd.id " .
               "       and pd.current_roll = ere.electoral_roll " .
               "       and ere.person = p.id " .
               "       and ere.address = a.id " .
		       "       and (";

		if (strlen ($name) > 0)
		{
			$sql .= "(p.surname like '%" . $name . "%' or " .
		       "            p.first_names like '%" . $name . "%')";

		    if (strlen ($address) > 0)
		    {
		       $sql .= " and ";
		    }
		}


		if (strlen ($address) > 0)
		{
		    $sql .= " (a.address1 like '%" . $address . "%' or " .
		       "            a.address2 like '%" . $address . "%' or " .
		       "            a.address3 like '%" . $address . "%' or " .
		       "            a.address4 like '%" . $address . "%' or " .
		       "            a.address5 like '%" . $address . "%') ";
		}

		$sql .= ") " .
		        "       order by ere.electoral_roll, ere.id ";

		return $sql;
	}
/*	
    protected function quick_search_sql ($roll, $name, $address)
    {
        $sql = "select ere.polling_district, ere.voter_number, ere.voter_number_suffix, ere.person, p.surname, p.first_names, " .
               "       a.address1, a.address2, a.address3, a.address4, a.address5, a.postcode, ere.vote_type " .
               "       from electoral_roll_entry ere, person p, address a " .
               "       where ere.electoral_roll = " . $roll .
               "       and ere.person = p.id " .
               "       and ere.address = a.id " .
               "       and (";

        if (strlen ($name) > 0)
        {
            $sql .= "(p.surname like '%" . $name . "%' or " .
               "            p.first_names like '%" . $name . "%')";

            if (strlen ($address) > 0)
            {
               $sql .= " and ";
            }
        }


        if (strlen ($address) > 0)
        {
            $sql .= " (a.address1 like '%" . $address . "%' or " .
               "            a.address2 like '%" . $address . "%' or " .
               "            a.address3 like '%" . $address . "%' or " .
               "            a.address4 like '%" . $address . "%' or " .
               "            a.address5 like '%" . $address . "%') ";
        }

        $sql .= ") " .
                "       order by ere.id ";

        return $sql;
    }
*/    
    public function get_canvass_sheet($roll, $street, $num, $offset)
    {
        $sql = $this->canvass_sheet_sql2($roll, $street);
        
        // if $num < 0, we want all entries
        if ($num < 0)
        {
            $query = $this->db->query($sql, array($offset, $num));
        }
        else
        {
            // paginate
            $sql .= "       limit ?, ?";
            $query = $this->db->query($sql, array($offset, $num));
        }

        return $query->result();
    }

    public function count_canvass_sheet($roll, $street)
    {
        $sql = $this->canvass_sheet_sql2($roll, $street) ;

//      echo "sql: " . $sql . "<br/>";

        $query = $this->db->query($sql);

        return $query->num_rows();
    }
    
/* Obsolete?
    protected function canvass_sheet_sql ($roll, $street)
    {
        $sql = "select a.address1, a.address2, p.surname, p.first_names, p.bday_18, max(e.election_date) last_voted, e.election_type, max(c.contact_date) last_contacted, c.rank, c.party, c.notes" .
               "    from electoral_roll_entry ere, address a, person p" .
               "    left join (voting_info vi, election e) on (p.id = vi.person and vi.election = e.id)" .
               "    left join contact c on p.id = c.person" .
               "    where ere.electoral_roll = " . $roll .
               "    and ere.person = p.id" .
               "    and ere.address = a.id" . 
               "    and (a.address1 like '%" . $street . "%')" .
               "    group by p.id" .
               "    order by ere.id";

        return $sql;
    }
*/
    
    protected function canvass_sheet_sql2 ($zone, $street)
    {
        $sql = "select p.id, a.address1, a.address2, p.surname, p.first_names, p.bday_18, p.first_seen_date" .
               "    from electoral_roll_entry ere, zone_district zd, polling_district pd, address a, person p, electoral_roll roll" .
               "       where zd.zone = $zone " .
               "       and zd.polling_district = pd.id " .
               "       and pd.current_roll = ere.electoral_roll " .
               "    and ere.person = p.id" .
               "    and ere.address = a.id" . 
               "    and (a.address1 like '%" . $street . "%' or a.address2 like '%" . $street . "%' or a.address3 like '%" . $street . "%')" .
               "    group by p.id" .
               "    order by ere.electoral_roll, ere.id";

        return $sql;
    }
/*    
    protected function canvass_sheet_sql2 ($roll, $street)
    {
        $sql = "select p.id, a.address1, a.address2, p.surname, p.first_names, p.bday_18, roll.compile_date first_seen_date" .
               "    from electoral_roll_entry ere, address a, person p, electoral_roll roll" .
               "    where ere.electoral_roll = " . $roll .
               "    and ere.person = p.id" .
               "    and roll.id = p.first_seen_roll_id" .
               "    and ere.address = a.id" . 
               "    and (a.address1 like '%" . $street . "%' or a.address2 like '%" . $street . "%' or a.address3 like '%" . $street . "%')" .
               "    group by p.id" .
               "    order by ere.id";

        return $sql;
    }
*/    
	
}
?>
